from backend.cews.models import Log,System, RepairOrder, RepairOrderRepairDetails, Parts, Asset, RepairOrderPart, TrainborneMaintenanceRecords, PartsReplacement, StockItemGroup, StockItem, Workgroup
from django.db.models import Q
from datetime import datetime, timedelta,date as Date
from dateutil.relativedelta import relativedelta
from functools import partial
from django.utils import timezone
from django.http import HttpResponse
from reportlab.platypus import  Table, TableStyle,Image ,Paragraph, Frame, PageTemplate, BaseDocTemplate
from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet
from reportlab.lib.pagesizes import landscape, letter
from django.db.models import Max , Min
from reportlab.lib import colors
from reportlab.lib.units import cm
from django.conf import settings
from typing import List, Dict
from backend.schema.upload_csv_schema import CmWeeklySummaryQueryIndex, IncidentViewQueryIndex,  IncidentViewNotModifiedIndex, CmWeeklySummaryNotModifiedIndex
import chardet 
import csv            
import io
import codecs
import os

def repair_order_history(stock_code,equipment_class,level,location,dateFrom,dateTo):
    results = []
    
    query = Q()

    if location:
        query &= Q(repair_order__location__icontains=location)

    # if dateFrom and dateTo:
    #     query &= Q(timestamp__range=[dateFrom, dateTo])

    parts = Parts.objects.filter(deleted=False, obsolescence=1)

    p_dict = {
        p.stock_code + p.serial_no : p.obsolescence
        for p in parts
    }
    
    details = RepairOrderRepairDetails.objects.select_related('repair_order').filter(deleted=False).filter(query)
    
    for detail in details:
        # repairing_detail = RepairOrderRepairDetails.objects.get(repair_order=order)
        # if detail.repair_order.status == RepairOrder.STATUS.CHECKED_IN:
        #     continue
        if stock_code!='' and stock_code!=detail.repair_order.primary_part.stock_code:
            continue

        if stock_code!='' and level == '4':
            p = Parts.objects.get(stock_code=detail.repair_order.primary_part.stock_code,serial_no=detail.repair_order.primary_part.serial_no)
            if not p.is_parent:
                continue
        if stock_code!='' and level == '5':
            p = Parts.objects.get(stock_code=detail.repair_order.primary_part.stock_code,serial_no=detail.repair_order.primary_part.serial_no)
            if p.is_parent:
                continue
            
        if equipment_class!='':
            p = Parts.objects.get(stock_code=detail.repair_order.primary_part.stock_code,serial_no=detail.repair_order.primary_part.serial_no)
            if p.equipment_class != equipment_class:
                continue


        key = detail.repair_order.primary_part.stock_code+detail.repair_order.primary_part.serial_no

        data = {}
        data["id"]  = detail.repair_order.id
        data["jo"]  = detail.repair_order.order_no
        data["system"]  = detail.repair_order.system
        data["serial_no"]  = detail.repair_order.primary_part.serial_no
        obsolescence = p_dict.get(key) 
        if obsolescence:
            data["obsolescence"]  = 1
        else:
            data["obsolescence"]  = 0
            
        data["stock_code"]  = detail.repair_order.primary_part.stock_code
        data["workgroup"]  = detail.repair_order.workgroup
        data["deliver_to"]  = detail.repair_order.deliver_to
        data["check_in"]  = detail.repair_order.check_in
        data["check_out"]  = detail.repair_order.check_out
        data["part_id"]  = detail.repair_order.primary_part.parts.id
        data["status"]  = detail.repair_order.status
        data["repair_details"] = detail.repair_details
        data["is_parent"] = detail.repair_order.primary_part.is_parent
        data["location"] = detail.repair_order.location
        data["test_result"] = detail.test_result
        data["actions"] = detail.actions
        data["remarks"] = detail.remarks

        results.append(data)

    return results

def repair_order_nff_records():
    
    parts = Parts.objects.filter(deleted=False, obsolescence=1)

    p_dict = {
        p.stock_code + p.serial_no : p.obsolescence
        for p in parts
    }

    results = []
    details = RepairOrderRepairDetails.objects.select_related('repair_order').filter(repair_details="FAULT CANNOT BE REPEATED (CM)",deleted=False)
    for detail in details:
        # repairing_detail = RepairOrderRepairDetails.objects.get(repair_order=order)
        # if detail.repair_order.status == RepairOrder.STATUS.CHECKED_IN:
        #     continue
        key = detail.repair_order.primary_part.stock_code+detail.repair_order.primary_part.serial_no

        data = {}
        data["id"]  = detail.repair_order.id
        data["jo"]  = detail.repair_order.order_no
        data["system"]  = detail.repair_order.system
        data["serial_no"]  = detail.repair_order.primary_part.serial_no
        obsolescence = p_dict.get(key) 
        if obsolescence:
            data["obsolescence"]  = 1
        else:
            data["obsolescence"]  = 0
        data["stock_code"]  = detail.repair_order.primary_part.stock_code
        data["workgroup"]  = detail.repair_order.workgroup
        data["deliver_to"]  = detail.repair_order.deliver_to
        data["check_in"]  = detail.repair_order.check_in
        data["check_out"]  = detail.repair_order.check_out
        # data["sw_ver"]  = detail.repair_order.primary_part.parts.sw_ver
        data["part_id"]  = detail.repair_order.primary_part.parts.id
        data["status"]  = detail.repair_order.status
        data["is_parent"] = detail.repair_order.primary_part.is_parent
        data["repair_details"] = detail.repair_details

        results.append(data)

    return results

def repair_order_detail_overview(system, year):
    query = Q()
    if system != "ALL":
        query &= Q(repair_order__system=system)

    if year != "ALL":
        query &= Q(repair_order__complete_datetime__year=year)
    
    total = 0

    results = {
        "total": total,
        "data" : []
    }
    
    details = [
        "NOT A FAULT (E.G PM, IGI)",
        "FAULT FOUND (CM)",
        "FAULT CANNOT BE REPEATED (CM)"
    ]


    for detail in details:
        count = RepairOrderRepairDetails.objects.filter(repair_details=detail,deleted=False).filter(query).count()
        results["data"].append({"value":count})
        total += count
        ## for pie graph
        ##results.append( {"value": count, "name": detail})
    return results

def inout_summary():


    last7day_check_in = RepairOrder.objects.filter(deleted=False, check_in__gte=datetime.now()-timedelta(days=7)).count()
    last30day_check_in = RepairOrder.objects.filter(deleted=False, check_in__gte=datetime.now()-timedelta(days=30)).count()
    total_check_in = RepairOrder.objects.filter(deleted=False).count()

    last7day_check_out = RepairOrder.objects.filter(deleted=False, check_out__gte=datetime.now()-timedelta(days=7)).count()
    last30day_check_out = RepairOrder.objects.filter(deleted=False, check_out__gte=datetime.now()-timedelta(days=30)).count()
    total_check_out = RepairOrder.objects.filter(deleted=False).exclude(check_out=None).count()

    data = {
        "last7" : [{"value": last7day_check_in, "name": "In"}, {"value": last7day_check_out, "name": "Out"}],
        "last30" : [{"value": last30day_check_in, "name": "In"}, {"value": last30day_check_out, "name": "Out"}],
        "total" : [{"value": total_check_in, "name": "In"}, {"value": total_check_out, "name": "Out"}]
    }

    return data

def registration_summary():

    dates = []
    values = []

    current_date = Date.today()
    # past_two_year = current_date - relativedelta(years=default)
    for i in range(24):
        next_month = current_date + relativedelta(months=-i)
        dates.insert(0, next_month)

    for date in dates:
        count = Parts.objects.filter(deleted=False, create__year=date.year, create__month=date.month).count()
        values.append(count)

    return {
        "date" : dates,
        "value" : values,
    }

def outgoing_record(year,month):
    def getCategory(day):
        if day>60:
            return '>60 days'
        if day >=31 and day <= 60:
            return '31~60 days'
        if day >=8 and day <= 30:
            return '8~30 days'
        if day >= 1 and day <= 7:
            return '1~7 days'
        return '0 days'

    system_list = []

    ret = {
        'data':[],
        '>60 days' : [0,0,0,0,0,0,0],
        '31~60 days' : [0,0,0,0,0,0,0],
        '8~30 days' : [0,0,0,0,0,0,0],
        '1~7 days' : [0,0,0,0,0,0,0],
        '0 days' : [0,0,0,0,0,0,0]
    }

    sys = System.objects.all()
    system_list = [s.system for s in sys]
    # for s in system_list:
    #     ret[s] = {
    #         '>60 days' : 0,
    #         '8~30 days' : 0,
    #         '1~7 days' : 0,
    #         '0 days' : 0
    #     }


    query = Q()

    query &= Q(check_in__year=year,check_out__year=year)
    query &= Q(check_in__month=month) | Q(check_out__month=month)

    ros = RepairOrder.objects.filter(deleted=False).filter(query)        

    for r in ros:
        data={}
        data['id'] = r.id
        data['stock_code'] = r.primary_part.stock_code
        data['serial_no'] = r.primary_part.serial_no
        data['system'] = r.system
        data['description'] = r.primary_part.description
        data['sw_ver'] = r.primary_part.sw_ver
        data['check_in'] = r.check_in
        data['check_out'] = r.check_out
        rounded_dt1 = r.check_in.replace(hour=0, minute=0, second=0, microsecond=0)
        rounded_dt2 = r.check_out.replace(hour=0, minute=0, second=0, microsecond=0)
        delta = (rounded_dt2 - rounded_dt1)
        data['duration'] = delta.days
        data['category'] = getCategory(delta.days)
        index = system_list.index(r.system.replace('\t',''))
        ret[getCategory(delta.days)][index] += 1
        
        ret['data'].append(data)


    return ret



def chart_repair_time_of_various_workgroup(year: int, month: int) -> list:
    """_summary_
    Args:
        year (int): _description_
        month (int): _description_
    """
    def getCategory(day):
        if day>60:
            return '>60 days'
        if day >=31 and day <= 60:
            return '31~60 days'
        if day >=8 and day <= 30:
            return '8~30 days'
        if day >= 1 and day <= 7:
            return '1~7 days'
        return '0 days'

    
    workgruops = Workgroup.objects.all()
    work_group_list = [workgruop.workgroup for workgruop in workgruops]
    ret = {
        'data':[],
        '>60 days' : [0 for _ in range(len(work_group_list))],
        '31~60 days' : [0 for _ in range(len(work_group_list))],
        '8~30 days' : [0 for _ in range(len(work_group_list))],
        '1~7 days' : [0 for _ in range(len(work_group_list))],
        '0 days' : [0 for _ in range(len(work_group_list))],
        'workgroup': work_group_list
    }

    ros = RepairOrder.objects.filter(deleted=False,check_out__year=year,check_out__month=month)
    for r in ros:
        if r.mtce_type == RepairOrder.MTCE_TYPE.IGI:
            continue
        data={}
        data['id'] = r.id
        data['stock_code'] = r.primary_part.stock_code
        data['serial_no'] = r.primary_part.serial_no
        data['system'] = r.system
        data["work_group"] = r.workgroup
        data['description'] = r.primary_part.description
        data['sw_ver'] = r.primary_part.sw_ver
        data['check_in'] = r.check_in
        data['check_out'] = r.check_out
        rounded_dt1 = r.check_in.replace(hour=0, minute=0, second=0, microsecond=0)
        rounded_dt2 = r.check_out.replace(hour=0, minute=0, second=0, microsecond=0)
        delta = (rounded_dt2 - rounded_dt1)
        data['duration'] = delta.days
        data['category'] = getCategory(delta.days)
        index = work_group_list.index(r.workgroup.replace('\t',''))
        ret[getCategory(delta.days)][index] += 1
        ret['data'].append(data)
    return ret

def repair_order_detail_table(repair_detail, system, year):
    ret = {}
    print(repair_detail, system, year)

    query = Q()
    if system != "ALL":
        query &= Q(repair_order__system=system)

    if year != "ALL":
        query &= Q(repair_order__complete_datetime__year=year)

    results = []
    details = RepairOrderRepairDetails.objects.select_related('repair_order').filter(repair_details=repair_detail,deleted=False).filter(query)

    for detail in details:
        data={}
        data['id'] = detail.id
        data["system"]  = detail.repair_order.system
        data["serial_no"]  = detail.repair_order.primary_part.serial_no
        data["stock_code"]  = detail.repair_order.primary_part.stock_code
        data["workgroup"]  = detail.repair_order.workgroup
        data["deliver_to"]  = detail.repair_order.deliver_to
        data["check_in"]  = detail.repair_order.check_in
        data["check_out"]  = detail.repair_order.check_out
        results.append(data)

    return results


def get_spare_availability(year, month):

    ret = {
        'category' : [],
        'legend':[],
    }

    statuss = ['COMPLETE','COMPLETE*','New Register']


    # if filter == "Checked In":
    #     statuss.append('CHECKED IN')
    #     query &= Q(last_check_out__isnull=True, last_check_in__isnull=False)

    # if filter == "Checked Out":
    #     query &= Q(last_check_out__isnull=False)

    # if filter == "Fit In":
    #     query &= Q(car__exact='')

    # if filter == "IGI":
    #     ## to do
    #     query &= Q()


    parts = Parts.objects.select_related('last_order').filter(Q(deleted=False, car='', last_status__in=statuss) | Q(deleted=False, car='',last_status__in=['COMPLETE','COMPLETE*'], last_order__mtce_type='IGI'))

    part_dict = {
        p.lv2 : p
        for p in parts
    }

    for key in part_dict:
        ret['category'].append(key)
        
    ret['category'].sort()

    for p in parts:
        if not p.lv4 in ret['legend']:
            ret['legend'].append(p.lv4)
        
        if not p.lv4 in ret:
            ret[p.lv4] = []
            for index in range(len(ret['category'])):
                ret[p.lv4].append(0)

        c_index = ret['category'].index(p.lv2)
        ret[p.lv4][c_index] += 1
    
    # print(ret)
    return ret

def get_overhaul_index(year,month):

    last_16_months = []
    ret = {
            'category' : [],
            'overhaul_index':[],
            '3_month_average':[],
            'data':[]
        }
    
    
    current_month = datetime.now().month

    for i in range(16):
        # Calculate the month and year for the current iteration
        month = (current_month - i) % 12 or 12
        year = datetime.now().year - (current_month < i + 1)

        # Create a datetime object for the first day of the month
        first_day_of_month = datetime(year, month, 1)

        # Get the last day of the month by subtracting one day from the first day of the next month
        last_day_of_month = (first_day_of_month.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)

        # Print the date and year for the last day of the month
        # print(last_day_of_month.strftime("%Y-%m"))
        last_16_months.append(last_day_of_month)
        # print(last_day_of_month.year)

    # Get the current date
    current_date = datetime.now().date()

    # Calculate the date 15 months ago
    start_date = current_date - timedelta(days=365+120)
    # Filter the data using the date range
    data_set = RepairOrderPart.objects.filter(deleted=False)\
                .select_related('repair_order')\
                .filter(Q(repair_order__check_in__month__gte=start_date.month) & Q(repair_order__check_in__month__lte=current_date.month))\
                .filter(repair_order__mtce_type__in=['CM','PM']).exclude(serial_no='').order_by('-repair_order__check_in')
                    # .filter(stock_code='666666666',serial_no='333EEE')\

    old_data_set = TrainborneMaintenanceRecords.objects.filter(report_date__range=(start_date, current_date)).filter(mtce_type__in=['CM','PM'])

    whole_data_set = []
    data_set_dict = {}

    for data in data_set:
        if data.repair_order.check_in is not None:
            checkin = datetime.strftime(data.repair_order.check_in,"%Y-%m-%d")
        else:
            checkin = data.repair_order.check_in

        if data.repair_order.check_out is not None:
            checkout = datetime.strftime(data.repair_order.check_out,"%Y-%m-%d")
        else:
            checkout = data.repair_order.check_out

        if data.repair_order.complete_datetime is not None:
            complete = datetime.strftime(data.repair_order.complete_datetime,"%Y-%m-%d")
        else:
            complete = data.repair_order.complete_datetime
        
        d = {
            "stock_code": data.stock_code,
            "serial_no": data.serial_no,
            "mtce_type": data.repair_order.mtce_type,
            "check_in": checkin,
            "check_out": checkout,
            "complete_date": complete,
        }

        whole_data_set.append(d)

    for old_data in old_data_set:
        d = {
            "stock_code": old_data.stock_code,
            "serial_no": old_data.serial_no,
            "mtce_type": old_data.mtce_type,
            "check_in": old_data.receive_date,
            "check_out": old_data.report_date,
            "complete_date": old_data.eams_completed_date,
        }
        whole_data_set.append(d)


        # data_set_dict[data.stock_code+data.serial_no].append(d)


    whole_data_set.sort(key=lambda whole_data_set: datetime.strptime(whole_data_set['check_in'], "%Y-%m-%d"), reverse=True)

    for whole in whole_data_set:

        ret['data'].append(whole)

        stock_code = str(whole['stock_code'])
        serial_no = str(whole['serial_no'])
        if stock_code+serial_no not in whole_data_set:
            data_set_dict[stock_code+serial_no] = []
        data_set_dict[stock_code+serial_no].append(whole)

    clone_dict = data_set_dict.copy()

    for date in last_16_months:

        pm_count = 0
        cm_count = 0
        
        # for part in data_set_dict:
        for part in data_set_dict:
            # if not part == '49620702436i':
            #     continue
            part_pm_count = 0
            part_cm_count = 0
            for repair_order in data_set_dict[part]:
                check_in_obj = datetime.strptime(repair_order['check_in'], "%Y-%m-%d")
                check_year = check_in_obj.year
                check_month = check_in_obj.month
                same_month = check_year == date.year and check_month == date.month
                # if same_month:
                #     # print(f'{date} | {part} | {repair_order["mtce_type"]} | {repair_order["check_in"]}')
                #     print(date, repair_order) 
                
                # repair_order["mtce_type"] == 'CM'
                if same_month:
                    for repair_order_pm in clone_dict[part]:
                        # pprint.pprint(clone_dict[part])
                        if repair_order_pm["mtce_type"] == 'PM' and repair_order_pm["check_out"] is not None:
                            pm_checkout =  datetime.strptime(repair_order_pm['check_out'], "%Y-%m-%d")
                            difference = abs(pm_checkout - pm_checkout)
                            if difference <= timedelta(days=90):
                                # print("The date from PM check out to CM check in is less than 90 days")
                                part_pm_count = 1
                                part_cm_count = 1

                check_is_3month_pm = abs(check_in_obj - date)
                
                if not part_cm_count and repair_order["mtce_type"] == 'PM' and check_is_3month_pm <= timedelta(days=90):
                    part_pm_count = 1

            pm_count += part_pm_count
            cm_count += part_cm_count

        # print('cm_count', cm_count)
        # print('pm_count', pm_count)

        if cm_count and pm_count > 0:
            overhaul_index = 1 - (cm_count/ (pm_count))
            ret['category'].insert(0,date.strftime("%Y-%m"))
            ret['overhaul_index'].insert(0,round(overhaul_index,2)*100)
        else:
            ret['category'].insert(0,date.strftime("%Y-%m"))
            ret['overhaul_index'].insert(0,round(0,2)*100)
      
    list = []
    for index , element in enumerate(ret['overhaul_index']):

            # if len(list) == 3:
            #     list.pop(0)
            # else:
        if len(list) == 3:
            list.pop(0)
 
        list.append(element)
        # print('list', list)
        average = round(sum(list)/3, 2)

        ret['3_month_average'].append(average)

    for key in ret:
        if key == 'data':
            continue
        ret[key] = ret[key][3:15]

    return ret

def get_critical_hard_time_replacement(user):
    
    ret = []

    group_ids = [g.id for g in user.groups.all()]


    stock_item_group_dict = {
        sig.stock_code: sig
        for sig in StockItemGroup.objects.filter(group__in=group_ids)
    }

    p_replaces = PartsReplacement.objects.filter(deleted=False)

    for p_replace in p_replaces:
        if p_replace.last_replaced is None: continue
        if p_replace.parts.stock_code not in stock_item_group_dict: continue
        difference =  p_replace.next_replace.date() - datetime.now().date()
        # difference =  datetime.strptime('2023-11-10', "%Y-%m-%d") - datetime.strptime('2023-10-15', "%Y-%m-%d")
        # print(difference.days)
        if difference.days < 0:
            data = {}
            data['component'] = p_replace.component
            data['equipment'] = p_replace.parts.description
            data['serial_no'] = p_replace.parts.serial_no
            data['overdue_date'] = p_replace.next_replace
            data['status'] = 'overdue'
            ret.append(data)

        elif difference.days >= 0 and difference.days <= 30:
            data = {}
            data['component'] = p_replace.component
            data['equipment'] = p_replace.parts.description
            data['serial_no'] = p_replace.parts.serial_no
            data['overdue_date'] = p_replace.next_replace
            data['status'] = 'one_month_left'
            ret.append(data)
    

    # print(ret)
    return ret

def get_long_outstanding(year, month):
    ret = {
        'category' : [],
        'data':{
            "old":[]
        },
    }

    last_4_months = []

    current_month = datetime.now().month

    for i in range(4):
        # Calculate the month and year for the current iteration
        month = (current_month - i) % 12 or 12
        year = datetime.now().year - (current_month < i + 1)

        # Create a datetime object for the first day of the month
        first_day_of_month = datetime(year, month, 1)

        # Get the last day of the month by subtracting one day from the first day of the next month
        last_day_of_month = (first_day_of_month.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)

        # Print the date and year for the last day of the month
        # print(last_day_of_month.strftime("%Y-%m"))
        last_4_months.insert(0,last_day_of_month)
        # print(last_day_of_month.year)

    wgs = {
            ro.workgroup: ro
            for ro in RepairOrder.objects.filter(deleted=False)
    }

    for wg in wgs:
        if wg not in ret['data']:
            ret['data'][wg] = []

    for key in ret["data"]:
        for index in range(len(last_4_months)):
            ret["data"][key].append(0)

    old_data_set = TrainborneMaintenanceRecords.objects.filter(receive_date__gte=timedelta(days=730))
    
    for index, cur_date in enumerate(last_4_months):
        repair_orders = RepairOrder.objects.filter(deleted=False, check_in__month__lte=cur_date.month, check_in__year__lte=cur_date.year)
        for ro in repair_orders:
            if ro.workgroup and ro.workgroup != '':
                difference = abs(ro.check_in.date() - datetime.now().date())
                if difference > timedelta(days=183) and ro.check_out == None:
                    ret['data'][ro.workgroup][index] += 1

        for old_data in old_data_set:
            try:
                if old_data.receive_date.lower() == 'nil' or old_data.out_date.lower() == 'nil': 
                    continue
                date_format = "%Y-%m-%d"

                receive_date = datetime.strptime(old_data.receive_date, date_format).date()
                # out_date = datetime.strptime(old_data.out_date, date_format).date()
                today = datetime.now().date()
                difference = abs(receive_date - today)
                if difference > timedelta(days=183) and old_data.out_date == '' :
                    ret['data']["old"][index] += 1
            except Exception as error:
                print(error)
        ret['category'].append(cur_date)
            
    return ret

def repair_performance_summary(year, month):

    ret = {
        'category' : [],
        'data':{
            'nff':[],
            'ff':[],
            'scrapped':[],
            'other':[],
            'under_repair':[],
        },
    }
    start_date_str = f"{year}-{month}-01"
    end_date_str = f"{year}-{month}-31"

    # start_date_str = "2021-01-01"
    # end_date_str = "2023-12-31"
    
    old_records = TrainborneMaintenanceRecords.objects.filter(receive_date__range=[start_date_str, end_date_str],mtce_type='CM')
    old_records_dict = {
        old.id : {
            "stock_code": old.stock_code,
            "serial_no": old.serial_no,
            "order_status": '',
            "lv2": Parts.objects.get(stock_code=old.stock_code, serial_no=old.serial_no).lv2,
            "test_result": old.test_result,
            "repair_details": old.finding,
            "mtce_type": old.mtce_type,
            "scrapped" : old.action == 'scrapped'
        }
        for old in old_records if Parts.objects.filter(stock_code=old.stock_code, serial_no=old.serial_no).exists()
    }

    rords = RepairOrderRepairDetails.objects.select_related('repair_order').filter(deleted=False).filter(repair_order__mtce_type='CM',repair_order__check_in__month=month, repair_order__check_in__year=year)
    primary_part_dict = {
        rord.repair_order.id : {
            "stock_code": rord.repair_order.primary_part.parts.stock_code,
            "serial_no": rord.repair_order.primary_part.parts.serial_no,
            "order_status": rord.repair_order.status,
            "lv2": rord.repair_order.primary_part.parts.lv2,
            "test_result": rord.test_result,
            "repair_details": rord.repair_details,
            "mtce_type": rord.repair_order.mtce_type,
            "scrapped" : rord.scrapped
        }
        for rord in rords 
    }

    primary_part_dict.update(old_records_dict)


    cate_dict = {}

    for key in primary_part_dict:
        for order_key in primary_part_dict[key]:
            if order_key == 'lv2':
                cate = primary_part_dict[key][order_key]
                if cate not in cate_dict:
                    cate_dict[cate] = cate
            else:
                continue

    for key in cate_dict:
        ret['category'].append(key)


    def filter_category(key, value, param):
        print(key, value, param)
        return value['lv2'] == category

    def filter_ff(pair):
        key, value = pair
        return value['repair_details'] == 'FAULT FOUND (CM)'
    
    def filter_nff(pair):
        key, value = pair
        return value['repair_details'] == "FAULT CANNOT BE REPEATED (CM)"
    
    def filter_scrapped(pair):
        key, value = pair
        return value['scrapped'] == True
    
    def filter_other(pair):
        key, value = pair
        return value['repair_details'] != "FAULT CANNOT BE REPEATED (CM)" and value['repair_details'] != 'FAULT FOUND (CM)'
    
    def filter_under_repair(pair):
        key, value = pair
        return value['order_status'] == "CHECKED IN" and value['test_result'] == ''
    
    
    for category in ret["category"]:
        total = 0
        nff = 0
        ff = 0
        scrapped = 0
        other = 0
        under_repair = 0

        filter_func = partial(filter_category, param=category)
        filtered_primary_part_dict = dict(filter(lambda item: filter_func(*item), primary_part_dict.items()))

        total += len(filtered_primary_part_dict)
        ff += len(dict(filter(filter_ff, filtered_primary_part_dict.items())))
        nff += len(dict(filter(filter_nff, filtered_primary_part_dict.items())))
        scrapped += len(dict(filter(filter_scrapped, filtered_primary_part_dict.items())))
        other += len(dict(filter(filter_other, filtered_primary_part_dict.items())))
        under_repair += len(dict(filter(filter_under_repair, filtered_primary_part_dict.items())))
        
        ret['data']['ff'].append(round(ff/total*100, 2))
        ret['data']['nff'].append(round(nff/total*100, 2))
        ret['data']['scrapped'].append(round(scrapped/total*100, 2))
        ret['data']['other'].append(round(other/total*100, 2))
        ret['data']['under_repair'].append(round(under_repair/total*100, 2))
       
    return ret


def get_mean_time_between_failure(year: datetime) -> List[Dict]:
    """
    MTBF = 365 / (number_of_failure / fleet_size)
    """
    results = []

    fleet_size = RepairOrder.objects.filter(deleted=False).exclude(location__exact='').count()

    stock_queryset = StockItem.objects.only("stock_code", "description").filter(deleted=False)
    for stock in stock_queryset:
        data = {
            "stock_code": "",
            "short_description": "",
            "MTBF": ""
        }

        stock_code = stock.stock_code
        description = stock.description
        
        # 此单号-的所有维修零件单号
        repair_order_id_list = list(RepairOrderPart.objects.only("repair_order_id").filter(stock_code=stock_code, deleted=False).values_list("repair_order_id", flat=True))
        repair_order_queryset = RepairOrder.objects.only("id").filter(check_out__gte=year, id__in=repair_order_id_list, deleted=False)

        # 损坏的数量
        number_of_failure = 0
        for repair_order in repair_order_queryset:
            repair_order_detail_count = repair_order.repairorderrepairdetails_set.filter(deleted=False, test_result=RepairOrderRepairDetails.TestResultType.FF).count()
            number_of_failure += repair_order_detail_count

        data["stock_code"] = stock_code
        data["short_description"] = description

        if fleet_size == 0 or number_of_failure == 0:
            data["MTBF"] = "NAN"
        else:
            data["MTBF"] = str(round(365 / (number_of_failure/ fleet_size), 2))

        results.append(data)
    return sorted(results, key=lambda x: x["MTBF"])



def get_spare_location_report(stock_code: str, serial_no: str, system: str, work_group: str, location: str) -> List[Dict]:
    results = []
    query = Q()
    if stock_code != "":
        query &= Q(stock_code=stock_code)
    if serial_no != "":
        query &= Q(serial_no=serial_no)
    if system != "":
        query &= Q(system=system)
    if work_group != "":
        query &= Q(workgroup=work_group)
    if location != "":
        query &= Q(location=location)

    parts_queryset = Parts.objects.filter(deleted=False,car="", last_check_out__isnull=False).filter(query)
    for part_obj in parts_queryset:
        data = {
            "stock_code": "",
            "serial_no": "",
            "system": "",
            "work_group": "",
            "location": ""
        }
        data["stock_code"] = part_obj.stock_code
        data["serial_no"]  = part_obj.serial_no
        data["work_group"] = part_obj.workgroup
        data["system"] = part_obj.system
        data["location"] = part_obj.location

        results.append(data)
    return results

def get_incident_view(parent_work_order_no:str, start_date: str, end_date: str) -> HttpResponse:
    status_list = [choices[0] for choices in RepairOrder.STATUS.choices if choices[0] != RepairOrder.STATUS.CHECKED_OUT]

    query = Q()
    if start_date and end_date:
        start_date = datetime.strptime(start_date, "%Y/%m/%d") - timedelta(days=1)
        end_date = datetime.strptime(end_date, "%Y/%m/%d") 
        query &= Q(repair_order__report_date__range=(start_date, end_date))
    else:
        if start_date != "":
            start_date = datetime.strptime(start_date, "%Y/%m/%d")
            query &= Q(repair_order__report_date__gte=start_date)
        
        if end_date != "":
            end_date = datetime.strptime(end_date, "%Y/%m/%d") 
            query &= Q(repair_order__report_date__lte=end_date)

    if parent_work_order_no != "":
        query &= Q(repair_order__parent_work_order_no=parent_work_order_no)

    # repair_order_part_queryset = RepairOrderPart.objects.select_related("repair_order").prefetch_related('child_set__toy_set').order_by('-id').filter(status__in=status_list, parent_part__isnull=True,repair_order__mtce_type=RepairOrder.MTCE_TYPE.CM).filter(query)
    repair_order_part_queryset = RepairOrderPart.objects.prefetch_related('repair_order__repairorderrepairdetails_set').order_by('-id').filter(status__in=status_list, parent_part__isnull=True,repair_order__mtce_type=RepairOrder.MTCE_TYPE.CM).filter(query)
    if repair_order_part_queryset.count() == 0:
        raise ValueError("Data not found")
    csv_header = [
        "Line Work Order No", "Work Nature Level 1", "Reported Date", "Location", "Incident Description", 
        "Failure Symptom", "Remark", "Incident Follow Up", "Delay (I)", "Delay (A)", 
        "CEWS Work order", "Stock code", "Equipment Description", "Serial Number", "Check In Date", 
        "Workgroup", "Test Result", "Follow up actions", "Check out date"
    ]

    csv_fields = [
        "line_work_order","Level_1", "reported_date", "location", "detail_description",
        "failure_symptom","remark","incident_follow_up","delay_i","delay_a",
        "work_order_no","stock_code","incident_description","serial_no","check_in",
        "workgroup","test_result","follow_up_actions","last_check_out"
    ]
 
    cvs_data_row_list= []
    for repair_order_part in repair_order_part_queryset:
        data = { field:" " for field in csv_fields}
        
        if r_o := repair_order_part.repair_order:
            data["line_work_order"] = r_o.parent_work_order_no
            data["Level_1"] = r_o.mtce_type
            if report_date := r_o.report_date:
                data["reported_date"] = report_date
            data["location"] = r_o.location 
            data["failure_symptom"] = r_o.failure_symptom + "\t"
            data["remark"] = r_o.remark + "\t"
            data["delay_i"] = r_o.delay_i + "\t"
            data["delay_a"] = r_o.delay_a + "\t"
            data["work_order_no"] = r_o.work_order_no + "\t"
            if check_in := r_o.check_in:
                check_in = timezone.localtime(check_in)
                data["check_in"] = check_in.strftime("%Y/%m/%d %H:%M:%S") + "\t"
            if check_out := r_o.check_out:
                check_out = timezone.localtime(check_out)
                data["last_check_out"] = check_out.strftime("%Y/%m/%d %H:%M:%S") + "\t"
            data["workgroup"] = r_o.workgroup
            if detail_queryset := r_o.repairorderrepairdetails_set.all():
                detail_obj = detail_queryset.first()
                data["test_result"] = detail_obj.test_result
                data["follow_up_actions"] =  detail_obj.maintenance_details + detail_obj.repair_details

        data["detail_description"] = "To be Comfirm"
        data["serial_no"] = str(repair_order_part.serial_no) + "\t"
        data["stock_code"] = str(repair_order_part.stock_code) + "\t"

        data["incident_description"] = repair_order_part.detail_description
        data["incident_follow_up"] = "To be Comfirm"

        data_one_row = [data[filed] for filed in csv_fields]

        cvs_data_row_list.append(data_one_row)

    response = csv_response(csv_header, cvs_data_row_list, file_name="incident_view.csv")
    return response

def upload_Incident_view(reader):
    """用户上传csv, 修改数据库"""
    csv_header = [
        "Line Work Order No", "Work Nature Level 1", "Reported Date", "Location", "Incident Description", 
        "Failure Symptom", "Remark", "Incident Follow Up", "Delay (I)", "Delay (A)", 
        "CEWS Work order", "Stock code", "Equipment Description", "Serial Number", "Check In Date", "Workgroup", "Test Result", "Follow up actions", "Check out date"
    ]
    if csv_header != next(reader):
        return None ,["Do not modify the file header"]
    # update fields
    failure_symptom_index =csv_header.index("Failure Symptom")
    remark_index = csv_header.index("Remark")
    delay_i_index = csv_header.index("Delay (I)")
    delay_a_index = csv_header.index("Delay (A)")
    cews_work_order_index =csv_header.index("CEWS Work order")
    # incident_description_index = csv_header.index("Incident Description")
    # incident_follow_up_index = csv_header.index("Incident Follow Up")
    # follow_up_actions_index = csv_header.index("Follow up actions")

    # query fields
    query_index = IncidentViewQueryIndex(
        check_out_date_index=csv_header.index("Check out date"),
        check_in_date_index=csv_header.index("Check In Date"),
        serial_no_index=csv_header.index("Serial Number"),
        stock_code_index=csv_header.index("Stock code"),
    )

    # Fields that cannot be modified
    not_field = IncidentViewNotModifiedIndex(
            parent_work_order_no=csv_header.index("Line Work Order No"),
            mtce_type=csv_header.index("Work Nature Level 1"),
            report_date= csv_header.index("Reported Date"),
            location=csv_header.index("Location"),
            detail_description= csv_header.index("Equipment Description"),
            workgroup=csv_header.index("Workgroup"),
            test_result=csv_header.index("Test Result")
        )


    r_o_update_list = []
    row_count = 1
    error_list = []
    for row in reader:
        row_count += 1
        query, error_msg = IncidentViewQueryIndex.upload_csv_upload_Incident_view_vaildata(row, query_index)
        repair_order_part_queryset = RepairOrderPart.objects.filter(parent_part__isnull=True).filter(query)
        if repair_order_part_queryset.count() == 1:
            r_o_p = repair_order_part_queryset.first()
            not_field.upload_incident_view_not_field_vaildata(row,row_count, r_o_p, error_list)
            update_model_fields = {
                "failure_symptom":row[failure_symptom_index].replace("\t", ""), 
                "remark":row[remark_index].replace("\t", ""), 
                "delay_i":row[delay_i_index].replace("\t", ""), 
                "delay_a":row[delay_a_index].replace("\t", ""), 
                "work_order_no": row[cews_work_order_index].replace("\t", "")
            }
            if r_o := r_o_p.repair_order:
                for field, data in update_model_fields.items():
                    setattr(r_o, field, data)
                r_o_update_list.append(r_o)
        else:
            if not error_msg["name"]:
                error_msg["name"] = "Serial Number or Stock code"
                error_msg["col"] = f"{query_index.stock_code_index + 1}:{chr(query_index.stock_code_index + 65)} or {query_index.serial_no_index + 1}:{chr(query_index.serial_no_index + 65)}"
            error_msg["row"] = str(row_count)
            error_list.append(f"Please do not modify the data in row {error_msg['row']}, columns error{error_msg['col']}")
            if len(error_list)>10:
                return None, error_list

    if len(error_list):
        return None, error_list
        
    chunk_size = 10
    update_count = 0
    for i in range(0, len(r_o_update_list), chunk_size):
        count = RepairOrder.objects.bulk_update(r_o_update_list[i:i+chunk_size],update_model_fields.keys())
        update_count += count
        if count != len(r_o_update_list[i:i+chunk_size]):
            return None, [f"Abnormal modification quantity in lines {i} to {i+chunk_size}: {count}"]
    return update_count, None

def get_igi_report(stock_code: str, start_date: str, end_date: str,completed: bool, username: str) -> HttpResponse:
    pdf_header = [
        "No", "INSPECTION No", "RECEIPT No", "RECEIPT DATE", "ITEM NO", 
        "ITEM DESCRIPTION", "PO/CON No", "REL No", "LINE NO", "SHIP NO",
        "REC QTY", "ACC QTY", "REJ QTY", "SHO QTY", "INSP CODE", 
        "QA ID", "NCA NO", "RT NO"
    ]
    pdf_fields = [
            "no",
            "inspection_no",
            "receipt_no",
            "received_date",
            "item_no",
            "item_description",
            "po_no",
            "rel_no",
            "line_no",
            "ship_no",
            "rec_qty",
            "acc_qty",
            "rej_qty",
            "sho_qty",
            "insp_code",
            "qa_id",
            "nca_no",
            "rt_no",
        ]
    query = Q()
    if stock_code:
        query &= Q(stock_code=stock_code)
    
    # 时间范围查询, 同时存在查询范围, 单个或不输入则查询最大和最小日期..
    if start_date and end_date:
        start_date = timezone.make_aware(datetime.strptime(start_date, "%Y/%m/%d"), timezone.get_current_timezone()).date()
        end_date = timezone.make_aware(datetime.strptime(end_date, "%Y/%m/%d"), timezone.get_current_timezone()).date()
        query &= Q(repair_order__check_in__range=(start_date, end_date + timedelta(days=1)))
    else:
        max_date = RepairOrder.objects.all().aggregate(Max('check_in')).get("check_in__max")
        min_date = RepairOrder.objects.all().aggregate(Min('check_in')).get("check_in__min")
        if start_date:
            start_date = timezone.make_aware(datetime.strptime(start_date, "%Y/%m/%d"), timezone.get_current_timezone()).date()
            if max_date:
                end_date = max_date.date()
            query &= Q(repair_order__check_in__gte=start_date)
        elif end_date:
            end_date = timezone.make_aware(datetime.strptime(end_date, "%Y/%m/%d"), timezone.get_current_timezone()).date()
            if min_date:
                start_date = min_date.date()
            query &= Q(repair_order__check_in__lte=end_date+ timedelta(days=1))
        else:
            start_date = end_date = timezone.now().date()
            if max_date: 
                end_date = max_date.date()
            if min_date: 
                start_date = min_date.date()

    if completed:
        query &= Q(repair_order__check_out__isnull=False)

    repair_order_part_queryset = RepairOrderPart.objects.filter(deleted=False, repair_order__mtce_type="IGI", parent_part__isnull=True).filter(query)

    data_rows = []
    for index, repair_order_part in enumerate(repair_order_part_queryset,1):
        data = { field:"-" for field in pdf_fields}
        data["no"] = str(index)
        data["item_no"] = repair_order_part.stock_code
        data["po_no"] = repair_order_part.repair_order.po_no
        data["inspection_no"] = repair_order_part.repair_order.inspection_no
        data["received_date"] = repair_order_part.repair_order.received_date
        data["item_description"] = repair_order_part.detail_description
        data_row = [data[field] for field in pdf_fields]
        data_rows.append(data_row)

    response = pdf_response(pdf_header, data_rows , "igi_report.pdf",start_date, end_date, username)
    return response


def csv_response(csv_header: List[str], cvs_data_row_list: List[List], file_name: str) -> HttpResponse:
    """
    csv响应
    """
    csvfile = io.BytesIO()
    writer = csv.writer(codecs.getwriter("gbk")(csvfile))
    writer.writerow(csv_header)
    writer.writerows(cvs_data_row_list)
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = f"attachment; filename={file_name}"
    csvfile.seek(0)
    response.write(csvfile.getvalue())
    return response


def pdf_response(pdf_header: List[str], pdf_rows: List[List], file_name: str,start_date:Date , end_date: Date, username: str) -> HttpResponse:
    """
    pdf响应
    """
    for index, header_field in enumerate(pdf_header):
        pdf_header[index] = str(header_field).replace(" ", "\n")

    pdf_buffer = io.BytesIO()
    elements = []

    col_width = [25, 65, 40, 55, 65, 115, 40] + [35] * 11 # 表格宽度
    def draw_table(*args):
        table_style = TableStyle([
            ('FONTSIZE', (0, 0), (-1, -1), 7),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('BOX', (0, 0), (-1, -1), 0.25, colors.white), 
        ])
        styles = getSampleStyleSheet()
        styleN = styles['Normal']
        styleN.fontSize = 7
        styleN.wordWrap = 'CJK'
        data2 = [[Paragraph(cell, styleN) for cell in row] for row in args] # 自动换行,设置成段落
        table_part = Table(data2, colWidths=col_width, style=table_style)
        table_part.hAlign = "LEFT"
        return table_part

    def set_footer(canvas, doc):
        """
        设置页脚--页码
        :param canvas:Canvas类型  pdf画布
        :param doc:doc类型   整个pdf文件
        """  
        canvas.saveState()  
        pageNumber = canvas.getPageNumber()
        p = Paragraph(f"Page {pageNumber}") # 绘画文字段落
        p.wrap(3 * cm, 1 * cm) 
        p.drawOn(canvas, 400, 50)  

        p = Paragraph(f"Printed on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        p.wrap(6 * cm, 3 * cm) 
        p.drawOn(canvas, 20, 50)  

        p = Paragraph(f"Generate By: {username}")
        p.wrap(4 * cm, 4 * cm)  
        p.drawOn(canvas, 650, 50)  
        canvas.restoreState()


    def set_header(canvas, doc):
        """
        设置页眉
        :param canvas:Canvas类型  pdf画布
        :param doc:doc类型     整个pdf文件
        """
        canvas.saveState()
        img = Image(os.path.join(settings.BASE_DIR, "mtr_logo.png")) # 绘画图片
        img.drawWidth = 2.5*cm 
        img.drawHeight = 1*cm 
        img.drawOn(canvas, 14, 555)

        table_header = Table([pdf_header], colWidths=col_width) # 绘画表格   
        table_header_style = TableStyle([
            ('FONTNAME', (0, 0), (-1, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('TEXTCOLOR', (0, 0), (-1, -1), '#000000'),
            ('BOX', (0, 0), (-1, -1), 0.25, colors.white), 
        ])
        table_header.setStyle(table_header_style)
        table_header.wrap(20 *cm, 5 * cm)
        table_header.drawOn(canvas, 10, 495)

        canvas.setStrokeColor(colors.black)
        canvas.line(doc.leftMargin + 5, doc.height - 30 , doc.width + doc.leftMargin, doc.height - 30) # 画线
        canvas.line(doc.leftMargin + 5, doc.height -5 , doc.width + doc.leftMargin, doc.height -5 )

        top_font_style = ParagraphStyle(name="h3", fontSize=12, leading=21, alignment=1,fontName="Helvetica-Bold")
        top = Paragraph("Notification of Completion of Incoming Goods Inspection (CEWS)", top_font_style) # 绘画文字
        top.wrap(15 * cm, 5 * cm)  
        top.drawOn(canvas, 200, 545)  

        date_font_style = ParagraphStyle(name="date", fontSize=10, fontName="Helvetica-Bold")
        date_range = Paragraph(f"Completion Date : {start_date} to {end_date}", date_font_style)
        date_range.wrap(10 * cm, 4 * cm)  
        date_range.drawOn(canvas, 12, 535)  
        canvas.restoreState()
    
    # 主体内容
    if pdf_rows:
        elements.append(draw_table(*pdf_rows))
    else:
        elements.append(Paragraph("No Data"))

    pdf = BaseDocTemplate(pdf_buffer, pagesize=landscape(letter))
    pdf.leftMargin = 5	
    pdf.rightMargin = 5
    pdf.topMargin = 20	
    pdf.bottomMargin = 60

    # 页眉,页尾
    frame_footer = Frame(pdf.leftMargin, pdf.bottomMargin, pdf.width, pdf.height - 25, id='normal')
    template = PageTemplate(id='test', frames=frame_footer, onPage=set_header, onPageEnd=set_footer)
    pdf.addPageTemplates([template])

    pdf.build(elements)
    response = HttpResponse(content_type='application/pdf')
    response['Content-Disposition'] = f"attachment; filename={file_name}"
    response.write(pdf_buffer.getvalue())
    pdf_buffer.close()
    return response

def get_regular_check_management_eg_card_with_battery_report(serial_no: str, stock_code: str, description: str, cycle_month: int, incoming_cycle_month: int) -> List[Dict]:

    query = Q()
    if serial_no:
        query &= Q(serial_no=serial_no)
    if stock_code:
        query &= Q(stock_code=stock_code)
    
    if description:
        query &= Q(description__startswith=description)
    
    parts_queryset = Parts.objects.filter(
        deleted=False,last_check_in__isnull=False, car = "",
        ).values_list("id","stock_code","serial_no", "description", "last_check_in", "last_check_out","stock_item_regular_cycle_month", named=True).filter(query)
    def diff_in_months(start_date: datetime, end_date: datetime):
        """计算时间差月份
        Args:
            start_date (datetime): 开始时间
            end_date (datetime): 结束时间
        returns:
            int: 时间差月份向下取整
        """
        start_month = start_date.year * 12 + start_date.month + start_date.day // 30
        end_month = end_date.year * 12 + end_date.month + end_date.day // 30
        diff_months = end_month - start_month

        if diff_months < 1:
            return 0  
        else:
            return diff_months // 1

    results = []
    today = datetime.today()
    for parts_obj in parts_queryset:
        orders = RepairOrderPart.objects.filter(parts_id=parts_obj.id,stock_code=parts_obj.stock_code, serial_no=parts_obj.serial_no,deleted=False, last_check_in=parts_obj.last_check_in, parent_part__isnull=True)
        if orders.exists():
            data = {
                "serial_no": parts_obj.serial_no,
                "stock_code": parts_obj.stock_code,
                "description": parts_obj.description,
                "cycle_month": 0,
                "incoming_cycle_month": 0,
            }
            if parts_obj.last_check_out:
                data["cycle_month"] = diff_in_months(parts_obj.last_check_out, today)
            if (sub_incoming_cycle_month := parts_obj.stock_item_regular_cycle_month - data["cycle_month"]) >= 0:
                data["incoming_cycle_month"] = sub_incoming_cycle_month
            
            # 过滤小于等于的
            if ((cycle_month != 0) and (data["cycle_month"] > cycle_month)) or ((incoming_cycle_month != 0) and (data["incoming_cycle_month"] > incoming_cycle_month)):
                continue
            results.append(data)
    return sorted(results, key=lambda x: x["incoming_cycle_month"])
        


def get_cm_weekly_summary(parent_work_order_no: str,report_date:str):
    """下载csv
    """
    csv_header = [
        "Line", "Date/Time of Incident", "Loc./Car No.", "Symptom", "Delay(I)",
        "Delay(C)", "Incident - WD/CO/PU/CL", "Line Recovery -Action Taken", "Job No./ Work order No.", "Workshoop Findings", 
        "Faulty Unit Serial No.","History (6 m) of the Defecitive Car/Assembly(Last PM & failures)", "Follow-up actions"
    ]
    
    csv_fields = [
        "line", "date_time_of_incident", "loc_car_no", "symptom", "delay_i","delay_c", "incident_wd_co_pu_cl", "line_recovery_action_taken", "job_no_work_order_no", "workshop_findings", 
        "faulty_unit_serial_no", "history_6m_last_pm_failures", "follow_up_actions"
    ]    
    status_list = [choices[0] for choices in RepairOrder.STATUS.choices if choices[0] != RepairOrder.STATUS.CHECKED_OUT]
    
    query = Q()
    if report_date != "":
        start_date = datetime.strptime(report_date, "%Y/%m/%d") - timedelta(days=1)
        end_date = datetime.strptime(report_date, "%Y/%m/%d") + timedelta(days=6)
        query &= Q(repair_order__report_date__range=(start_date, end_date))

    if parent_work_order_no != "":
        query &= Q(repair_order__parent_work_order_no=parent_work_order_no)

    mtce_type_list = [RepairOrder.MTCE_TYPE.CM, RepairOrder.MTCE_TYPE.PM, RepairOrder.MTCE_TYPE.NM]
    repair_order_part_queryset = RepairOrderPart.objects.select_related("repair_order").order_by('-id').filter(status__in=status_list, parent_part__isnull=True, repair_order__mtce_type__in=mtce_type_list).filter(query)
    if repair_order_part_queryset.count() == 0:
        raise ValueError("Data not found")

    cvs_data_row_list = []
    for repair_order_part in repair_order_part_queryset:
        data = {field:"" for field in csv_fields}
        if r_o := repair_order_part.repair_order:
            if r_o.report_date:
                try:
                    if len(r_o.report_date.split(" ")) == 1:
                        r_date = datetime.strptime(r_o.report_date, "%Y-%m-%d")
                        data["date_time_of_incident"] = r_date.strftime("%d-%b-%y")  + "\t"
                    else:
                        r_date = datetime.strptime(r_o.report_date, "%Y-%m-%d %H:%M:%S")
                        data["date_time_of_incident"] = r_date.strftime("%d-%b-%y %H:%M")  + "\t"
                except:
                    data["date_time_of_incident"] = "" + "\t"
            data["loc_car_no"] = r_o.location + "\t"
            data["delay_i"] = r_o.delay_i + "\t"
            data["delay_c"] = r_o.delay_a + "\t"
            data["job_no_work_order_no"] =  r_o.parent_work_order_no + "\t"
            data["faulty_unit_serial_no"] = repair_order_part.serial_no + "\t"
            data["line"] = r_o.line + "\t"
            data["symptom"] = r_o.failure_symptom + "\t"
            if r_o.check_out:
                check_out = timezone.localtime(r_o.check_out).strftime("%d/%m/%Y %H:%M:%S")
                data["history_6m_last_pm_failures"] = "Last " + r_o.mtce_type + " check wascompleted on " + check_out
            else:
                check_out = ""
                data["history_6m_last_pm_failures"] = r_o.mtce_type + check_out 

            r_o_r_d_obj = RepairOrderRepairDetails.objects.get(repair_order_id = r_o.id)
            if r_o_r_d_obj.maintenance_details:
                data["follow_up_actions"] =  r_o_r_d_obj.maintenance_details + ";" + r_o_r_d_obj.repair_details
            else:
                data["follow_up_actions"] =  r_o_r_d_obj.repair_details

            data_one_row = [data[filed] for filed in csv_fields]
            cvs_data_row_list.append(data_one_row)

    return csv_response(csv_header,cvs_data_row_list,"cm_weekly_summary.csv")

def upload_cm_weekly_summary(reader):
    """上传csv"""
    csv_header = [
        "Line", "Date/Time of Incident", "Loc./Car No.", "Symptom", "Delay(I)",
        "Delay(C)", "Incident - WD/CO/PU/CL", "Line Recovery -Action Taken", "Job No./ Work order No.", "Workshoop Findings", 
        "Faulty Unit Serial No.","History (6 m) of the Defecitive Car/Assembly(Last PM & failures)", "Follow-up actions"
    ]
    if csv_header != next(reader):
        return None,["Do not modify the file header"]
    # update fields
    line_index = csv_header.index("Line")
    symptom_index = csv_header.index("Symptom")
    delay_i_index = csv_header.index("Delay(I)")
    delay_a_index = csv_header.index("Delay(C)")
    # incident_wd_co_pu_cl_index = csv_header.index("Incident - WD/CO/PU/CL")
    # line_recovery_action_taken_index = csv_header.index("Line Recovery -Action Taken")
    # workshoop_findings_index=csv_header.index("Workshoop Findings")

    # query fields
    query_index = CmWeeklySummaryQueryIndex(
        serial_no_index=csv_header.index("Faulty Unit Serial No."),
        date_time_of_incident=csv_header.index("Date/Time of Incident"),
        car_no=csv_header.index("Loc./Car No."),
        job_no_work_order_no=csv_header.index("Job No./ Work order No."),
        history_6m_last_pm_failures=csv_header.index("History (6 m) of the Defecitive Car/Assembly(Last PM & failures)"),
        )

    # not fields
    not_field = CmWeeklySummaryNotModifiedIndex(
        maintenance_details = csv_header.index("Follow-up actions"),
        repair_details = csv_header.index("Follow-up actions"),
    )


    row_count = 1
    error_list = []
    r_o_update_list = []
    for row in reader:
        row_count += 1

        # 校验
        query, error_msg = CmWeeklySummaryQueryIndex.cm_weekly_summary_vaildata(row, query_index)
        r_o_p_queryset = RepairOrderPart.objects.filter(parent_part__isnull=True).filter(query)
        update_model_fields = {
            "delay_i": row[delay_i_index].replace("\t", ""), 
            "delay_a": row[delay_a_index].replace("\t", ""), 
            "line": row[line_index].replace("\t", ""), 
            "failure_symptom": row[symptom_index].replace("\t", "")
            }
        if r_o_p_queryset.count() == 1:
            r_o_p = r_o_p_queryset.first()
            not_field.upload_cm_weekly_summary_not_field_validata(row, row_count, r_o_p, error_list)
            if r_o := r_o_p.repair_order:
                for field, data in update_model_fields.items():
                    setattr(r_o, field, data)
                r_o_update_list.append(r_o)
        else:
            if not error_msg["name"]:
                error_msg["name"] = "Serial Number or Stock code"
                error_msg["col"] = f"[{chr(query_index.car_no + 65)} or {chr(query_index.job_no_work_order_no + 65)} or {chr(query_index.serial_no_index + 65)}]"
            error_msg["row"] = str(row_count)
            error_list.append(f"Please do not modify the data in row {error_msg['row']}, columns error{error_msg['col']}")
            if len(error_list) > 10:
                return None, error_list

    # 异常报错
    if len(error_list):
        return None ,error_list
        
    # 执行修改
    chunk_size = 10
    update_count = 0
    for i in range(0, len(r_o_update_list), chunk_size):
        count = RepairOrder.objects.bulk_update(r_o_update_list[i:i+chunk_size],update_model_fields.keys())
        update_count += count
        if count != len(r_o_update_list[i:i+chunk_size]):
            return None ,[f"Abnormal modification quantity in lines {i} to {i+chunk_size}: {count}"]
    return update_count, None

def check_upload_csv_file(request):
    try:
        csvfile = request.FILES['file']
    except:
        return None ,"File not found"    
    csvfile = request.FILES['file']
    data = csvfile.read()
    filename = csvfile.name
    if not filename.endswith(".csv"):
        return None, "File type is not csv"
    if csvfile.size > 5000000:
        return None,"File size is greater than 5 MB"
    res = chardet.detect(data)
    encoding = res["encoding"]
    decoded_file = data.decode(encoding).splitlines()
    reader = csv.reader(decoded_file)  
    return reader, None